#include <stdio.h>
#include "sqlite3.h"
#define CREAT  "create table if not exists studen(id INTEGER primary key AUTOINCREMENT,name text, ip varchar(32) UNIQUE);"

#define INSERT "insert into studen values(NULL, '%s','%s');"
#define DELETE "delete  from studen where id=%d;"
#define SELECT "select * from studen;"
#define UPDATE "update studen set name='wanwu' where id=%d;"     //修改
#define DBNAME "mysql.db"

int f = 0;
int callback(void *arg, int col_n,  char **col_value, char **col_name)//回调函数，其中col_n为列的个数  col-alue为参数对值，col-name为参数对名称，每一行数据调用一次
{
	int i=0;
	if(f==0)
	{

		for(i=0; i<col_n; i++)					
		{
			printf("%s\t", col_name[i]);
		}
		printf("\n");
		f=1;
	}
	
	for(i=0; i<col_n; i++)
	{
		printf("%s\t", col_value[i]);
	}

	printf("\n");
	return 0;
}
void insert(sqlite3 *ppdb)        //插入数据
{
	char insert[128]={0};
	char name[32] = {0};
	char ip[32] = {0};

	printf("请输入名字:");scanf("%s", name);
	printf("请输入IP:");scanf("%s", ip);
	sprintf(insert,INSERT,name,ip);

	char *err = NULL;
	int ret = sqlite3_exec(ppdb, insert, NULL, NULL, &err); //err 数据库错误输出
	if(ret != SQLITE_OK)
	{
		printf("%s\n",err);
		perror("insert fail");
		return ;
	}	

}
void select(sqlite3 *ppdb)     //查询数据
{
	int ret = sqlite3_exec(ppdb, SELECT, callback, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		perror("select fail");
		return ;
	}	
}
void delete(sqlite3 *ppdb, int num) //删除数据
{
	char delect[256] = {0};
	sprintf(delect,DELETE,num);
	int ret = sqlite3_exec(ppdb, delect, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		perror("delect fail");
		return ;
	}
}

int main(void)
{
	sqlite3 *ppdb;
	int ret = sqlite3_open(DBNAME, &ppdb);  //打开数据库
	if(ret != SQLITE_OK)
	{
		perror("open fail");
		return -1;
	}	
	
	ret = sqlite3_exec(ppdb, CREAT, NULL, NULL, NULL); //创建表 第三个参数为回调函数，第四个参数为回调函数第1个参赛，第五个参数为数据库出错处理
	if(ret != SQLITE_OK)
	{
		perror("creat fail");
		return -1;
	}
	int number;
	while(1)
	{
		printf("*****1.插入数据*****\n");
		printf("*****2.查询数据*****\n");
		printf("*****3.删除数据*****\n");
		printf("*****0.  退出  *****\n");

		scanf("%d",&number);
		switch(number)
		{
			case 1:
			      	insert(ppdb);break;
			case 2: 
				select(ppdb);f=0;break;
			case 3:
				printf("请输入删除对id:\n");
				scanf("%d",&number);
				delete(ppdb,number);break;
		}
		if(number == 0)
			break;
	}	

	ret = sqlite3_close(ppdb); //关闭数据库

	return 0;
}
